<?php

/**
 * 导入excel文件helper类
 * @param  int $row 开始行数
 * @param  string $fields     插入数据库字段数组
 * @param  array  $index_arr  index数组
 * @param  string $input_name input名
 * @param  array  $extra 添加的额外字段
 * @return insert_batch 数组
 */
class Excel_helper
{

    private $CI;

    protected $excel_name = 'Excel';
    private $objPHPExcel;
    private $objDrawing;
    
    function __construct()
    {
        $this->CI = & get_instance();
        $this->CI->load->library('third_party/PHPExcel');
        $this->CI->load->library('third_party/PHPExcel/IOFactory');
        $this->objPHPExcel = new PHPExcel();
        $this->objDrawing = new PHPExcel_Worksheet_Drawing();
    }
    
    /**
     * 设置特性
     * @param  $properties[title,subject,description,keyword,category,manager]
     */
    public function set_properties( $properties = array() ){
        $allowed_key = array("lastModifiedBy","creator","title","subject","description","keywords","category","manager");
        foreach($properties as $key=>$value){
            if(in_array($key,$allowed_key)){
                $func_name  = 'set' . ucfirst($key);
                $this->objPHPExcel->getProperties()->$func_name($value);
            }
        }
    }
    /**
     * 设置标题
     */
    public function set_title( $title ){
        $this->objPHPExcel->getActiveSheet()->setTitle( iconv('gbk', 'utf-8',$title) );
    }
    /**
     * 设置列宽度
     * @param  $arr[列=>宽度]
     */
    public function set_colunm_widths($arr){
        if( !is_array($arr) ) return false;
        foreach($arr as $column=>$value){
             
            //宽度设置
            if(is_numeric($value) && $value>0){
                $this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($column)->setWidth($value);
            }else{
                $this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($column)->setAutoSize(true); //自适应
            }
            //是否可折叠
            if( isset($value['collapsed']) && $value['collapsed']===true){
                $this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($column)->setOutlineLevel(1);
                $this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($column)->setVisible(true);//初始状态为折叠
                $this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($column)->setCollapsed(true); //可折叠
                if( isset($value['visible']) && $value['visible']===false ){
                    $this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($column)->setVisible(true);//初始状态为打开
                }
            }
        }
         
    }
    /**
     * 设置内容
     * @param  $data[二维数组]
     */
    public function set_cell_value_from_array($data){
        if( !is_array($data) ) return false;
        //setCellValueByColumnAndRow('列数','行数','值') //列数始于0，行数始于1
    
        $row = 1;
        foreach ($data as $_row => $array) {
            if( !is_array($array) ) return false;
            $column = 0;
            foreach ($array as $_column => $value) {
                $column_str = PHPExcel_Cell::stringFromColumnIndex($column);// 0 转 A
                //$value = $value.' ';
                //直接赋值
                if( is_string($value) || is_numeric($value) ){
                    //$this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row, ''.$value);
                    $this->objPHPExcel->getActiveSheet()->setCellValueExplicit($column_str.$row, $value,PHPExcel_Cell_DataType::TYPE_STRING);
                }else{
                    if( !isset($value['value']) || !is_string($value['value']) ){
                        $value['value'] = '';
                    }
                    //样式设置
                    if( isset($value['style']) && is_array($value['style']) ){
                        $this->objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($column,$row)->applyFromArray($value['style']);
                    }
                    //行高度
                    if( isset($value['height']) && is_numeric($value['height']) && $value['height']>0 ){
                        $this->objPHPExcel->getActiveSheet()->getRowDimension($column)->setRowHeight($value['height']);
                    }
                    //列宽度
                    if( isset($value['width']) && is_numeric($value['width']) && $value['width']>0){
                        $this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($column)->setWidth($value['width']);
                    }
                    //超链接
                    if(isset($value['url']) && is_string($value['url'])){
                        $this->objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column,$row)->getHyperlink()->setUrl($value['url']);
                        $this->objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column,$row)->getHyperlink()->setTooltip('Navigate to website');
                    }
                    //插入图片
                    if( isset($value['image']) ){
                        //必要参数验证
                        $_necesarry_key = array('path','height','width');
                        $_check_nk_is_pass = true;
                        foreach($_necesarry_key as $_nk){
                            if( !isset($value['image'][$_nk]) || !is_string($value['image'][$_nk]) ){
                                $_check_nk_is_pass = false;
                            }
                        }
                        //验证通过
                        if( $_check_nk_is_pass ){
                            //Add a drawing to the worksheet
                            $_img_opt = $value['image'];
                            $this->objDrawing->setPath($value['image']['path']);
                            $this->objDrawing->setHeight( bcadd($value['image']['height'],0,2) );    //照片高度
                            $this->objDrawing->setWidth(  bcadd($value['image']['width'],0,2)  );    //照片宽度
                            if( isset($value['image']['name']) && is_string($value['image']['name']) ){//照片名
                                $this->objDrawing->setName($value['image']['name']);
                            }
                            if( isset($value['image']['desc']) && is_string($value['image']['desc']) ){//照片描述
                                $this->objDrawing->setDescription($value['image']['desc']);
                            }
                            if( isset($value['image']['setOffsetX']) && is_numeric($value['image']['setOffsetX']) ){//x方向初始位置
                                $this->objDrawing->setOffsetX( bcadd($value['image']['setOffsetX'],0,2) );
                            }
                            if( isset($value['image']['rotation']) && is_numeric($value['image']['rotation']) ){//x方向初始位置
                                $this->objDrawing->setRotation( bcadd($value['image']['rotation'],0,2) );
                            }
                            if( isset($value['image']['visible']) && is_bool($value['image']['visible']) ){//可见
                                $this->objDrawing->getShadow()->setVisible( $value['image']['visible'] );
                            }
                            if( isset($value['image']['direction']) && is_numeric($value['image']['direction']) ){//方向
                                $this->objDrawing->getShadow()->setDirection( bcadd($value['image']['direction'],0,2) );
                            }
                            $this->objDrawing->setCoordinates($column_str.$row);
                            $this->objDrawing->setWorksheet($this->objPHPExcel->getActiveSheet());
                        }
                    }
                     
                    //赋值
                    $this->objPHPExcel->getActiveSheet()->setCellValueExplicit($column_str.$row, $value['value'],PHPExcel_Cell_DataType::TYPE_STRING);
                    //$this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row,' '. $value['value']);
                    //单元合并
                    if( isset($value['merge']) ){
                        $col_width  = $column;
                        $row_height = $row;
                        if( isset($value['merge']['add_row']) ){
                            $row_height = abs(intval($value['merge']['add_row'])) + $row;
                        }
                        if( isset($value['merge']['add_col']) ){
                            $col_width  = abs(intval($value['merge']['add_col'])) + $column;
                        }
                        $this->objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($column,$row,$col_width,$row_height);
                    }
                     
                }//ENDIF  is_string($value)
    
                $column++;
            }//ENDFOREACH $array
             
            $row ++;
        }//ENDFOREACH $data
        //die;
        return true;
    }
    /**
     * 样式设置
     * $string_cell  [A1]一列第一格     [A1:Z1]第一行
     * $style  样式参数
     */
    public function set_style_by_string($string_cell,$style){
        if( !preg_match_all('/^([A-Z][1-9][0-9]*)(\:[A-Z][1-9][0-9]*)?$/',$string_cell) )
            return array('errorCode'=>1,'errorMessage'=>'参数[string_cell]格式错误');
        if( !is_array($style) || empty($style) )
            return array('errorCode'=>1,'errorMessage'=>'参数[style]格式错误');
        $this->objPHPExcel->getActiveSheet()->getStyle($string_cell)->applyFromArray($style);
    }
    /**
     * 列编号数字转字母
     * @param col_num 始于0
     * @param row_num 始于1
     */
    public function columnNumToString($col_num){
        p();
        $col_num = intval($col_num);
        return PHPExcel_Cell::stringFromColumnIndex($col_num);
    }
    /**
     * 返回最后一个单元格 J4
     * 注意：该方法，必须在填充数据之后,不然返回空
     */
    public function get_last_cell(){
        //所有单元格
        $cell = $this->objPHPExcel->getActiveSheet()->getCellCollection();
        return end($cell);
    }
    /**
     * 获取行数,列数
     * $return  未填充数据时,返回 ['row'=>1,'column'=>'A']
     */
    public function get_row_and_col(){
        return $this->objPHPExcel->getActiveSheet()->getHighestRowAndColumn();
    }
    
    /**
     * 保存导出文件
     */
    public function export_as_file_name($file_name='',$format='Excel5'){
        if( !in_array($format,array('Excel5',"Excel2007","CSV","HTML","PDF")) )
            return array('errorCode'=>1,'errorMessage'=>'参数[format]非法');
        if( !is_string($file_name) || $file_name==='')
            return array('errorCode'=>1,'errorMessage'=>'参数[file_name]类型错误');
    
        #第一个工作薄
        $this->objPHPExcel->setActiveSheetIndex(0);
        $objWriter = IOFactory::createWriter($this->objPHPExcel,$format);
        switch($format){
            case 'Excel5':
                header("Content-Type: application/vnd.ms-excel");
                $postfix = 'xls';
                break;
            case 'Excel2007':
                header("Content-Type: text/xml;charset=UTF-8");
                $postfix = 'xlsx';
                break;
            case 'CSV':
                header("Content-Type: application/pdf;charset=UTF-8");
                $postfix = 'csv';
                break;
            case 'HTML':
                header("Content-Type: text/html;charset=UTF-8");
                $postfix = 'html';
                break;
            case 'PDF':
                header("Content-Type: application/pdf;charset=UTF-8");
                $postfix = 'pdf';
                break;
            default:
                return array('errorCode'=>1,'errorMessage'=>'参数[format]非法');
        }
        header('Content-Disposition: attachment;filename="'.$file_name.'.'. $postfix .'"');
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header("Content-Transfer-Encoding:binary");
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
        return array('errorCode'=>0,'errorMessage'=>'导出成功');
    }
    
    /**
     * 根据mode,设置表样式
     * @param 总列数
     * @param 总行数
     * @param mode [1] 暂时只支持一种样式
     */
    public function set_excel_style_by_mode($mode='1'){
        if($mode!='1') return false;
    
        //设置首行样式
        $head_row_style = array(
            //字体设置
            'font' => array(
                'name'		=> 'Arial',
                'bold'		=> TRUE,
                'italic'	=> FALSE,
                //'underline' => PHPExcel_Style_Font::UNDERLINE_DOUBLE,
                'strike'	=> FALSE,
                'color'		=> array(
                    'rgb' => '#000000',
                )
            ),
            //背景色
            'fill'    =>array(
                'type'	     => PHPExcel_Style_Fill::FILL_SOLID,
                // 'type'	     => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation'   => 0,
                'startcolor' => array(
                    'argb'   => 'FF606060'
                ),
                'endcolor'   => array(
                    'argb'   => 'FF606060'
                )
            )
        );
        $other_row_style = array(
            'fill'    =>array(
                'type'	     => PHPExcel_Style_Fill::FILL_SOLID,
                // 'type'	     => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation'   => 0,
                'startcolor' => array(
                    'argb'   => 'FF999999'
                ),
                'endcolor'   => array(
                    'argb'   => 'FF999999'
                )
            )
        );
    
        $RowAndColumn = $this->get_row_and_col();
        $head_row  = 'A1:'.$RowAndColumn['column'] . "1";
        $other_row = 'A2:'.$RowAndColumn['column'] . $RowAndColumn['row'];
         
        $this->set_style_by_string($head_row ,$head_row_style);
        $this->set_style_by_string($other_row,$other_row_style);
    }
    
    
    /**
     * 导出excel
     * $foramt 支持5种文件格式[CSV,Excel2007,PDF,Excel5,HTML]
     * $data   数据,array( array(),array(),...  );
     * $column_widths 列宽度      ["A"=>20,..]
     * $properties    特性设置  [lastModifiedBy,creator,title,subject,description,keywords,category,manager]
     * @author Jerry
     */
    public function create_excel_from_array($data=array(),$title='',$format='Excel5',$column_widths=array(),$properties=array()){
        if( !is_string($title) || $title==='')
            return array('errorCode'=>1,'errorMessage'=>'参数[title]类型错误');
        if( !in_array($format,array('Excel5',"Excel2007","CSV","HTML","PDF")) )
            return array('errorCode'=>1,'errorMessage'=>'参数[format]非法');
        if( empty($data) || !is_array($data) )
            return array('errorCode'=>1,'errorMessage'=>'参数[data]类型错误');
    
    
        #特性设置
        $default_properties = array(
            "lastModifiedBy" => "",
            "creator"     =>  "",
            "title"	     =>  $title,
            "subject"     =>  date('Y-m-d H:i:s',time()),
            "description" =>  "",
            "keywords"	 =>  "",
            "category"    =>  "",
            "manager"     =>  ""
        );
        $properties = array_merge($default_properties,$properties);
        $this->set_properties($properties);
         
        #标题
        $encode = mb_detect_encoding($title, array("ASCII","UTF-8","GB2312","GBK","BIG5"));
        if( $encode === 'GBK' ){
            $this->objPHPExcel->getActiveSheet()->setTitle(iconv('gbk',   'utf-8',$title));
        }elseif( $encode === 'GB2312' ){
            $this->objPHPExcel->getActiveSheet()->setTitle(iconv('gb2312','utf-8',$title));
        }else{
            $this->objPHPExcel->getActiveSheet()->setTitle($title);
        }
         
         
        #列宽度
        if(!empty($column_widths)){
            $this->set_colunm_widths($column_widths);
        }
        //自适应
        /* if(1){
         for($i=0;$i<$column_number;$i++){
         $this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($i)->setAutoSize(true);
         }
         } */
         
        #表默认样式设置
        $defaultStyle = array(
            //居中
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'rotation'   => 0,
                'wrap'	     => TRUE
            ),
            //字体设置
            'font' => array(
                'name'		=> 'Arial',
                'bold'		=> FALSE,
                'italic'	=> FALSE,
                //'underline' => PHPExcel_Style_Font::UNDERLINE_DOUBLE,
                'strike'	=> FALSE,
                'color'		=> array(
                'argb' => '#000000',
                )
            )
        );
        $this->objPHPExcel->getDefaultStyle()->applyFromArray($defaultStyle);
         
        #数据填充
        $fill_ok = $this->set_cell_value_from_array($data);
        if($fill_ok){
            return array('errorCode'=>0,'errorMessage'=>'数据填充成功');
        }else{
            return array('errorCode'=>1,'errorMessage'=>'数据填充失败');
        }
    
    
    }
    
    
    /**
     * 快捷导出excel
     * @param $data
     */
    public function simple_export($data,$file_name,$format='Excel5',$column_widths=array()){
        if( !is_array($data) || empty($data) )
            return array('errorCode'=>1,'errorMessage'=>'excel导出失败，参数[data]出错');
        if( !is_string($file_name) )
            return array('errorCode'=>1,'errorMessage'=>'excel导出失败，参数[file_name]出错');
        if( $file_name ==='' ) $file_name='excel';
        if(!in_array($format,array('Excel5','Excel2007','HTML','CSV')))
            return array('errorCode'=>1,'errorMessage'=>'excel导出失败，参数[file_format]非法值');
         
        #1. excel 内容填充
        $create_excel = $this->create_excel_from_array($data,$file_name,$format,$column_widths);
        if($create_excel['errorCode']!=0){
            return $create_excel;
        }
    
        #2. 启用默认样式
        $this->set_excel_style_by_mode(1);
         
        #3. 导出
        $export_excel = $this->export_as_file_name($file_name,$format);
        return $export_excel;
    }
    
    private function get_date($val)
    {
        $jd = GregorianToJD(1, 1, 1970);
        echo "==" . $jd . "==";
        $gregorian = JDToGregorian($jd + intval($val) - 25569);
        return $gregorian;
    }

    public function get_excel_data($row, $fields = array(), $index_arr = array(), $extra = array(), $input_name = 'Filedata')
    {
        $objPHPExcel = IOFactory::load($_FILES[$input_name]["tmp_name"]);
        $data = $objPHPExcel->getSheet(0)->toArray();
        $count = count($data);
        
        $insert_batch = array();
        for ($i = $row; $i < $count; $i ++) {
            $insert = array();
            for ($j = 0; $j < count($fields); $j ++) {
                $insert += array(
                    $fields[$j] => $data[$i][$index_arr[$j]]
                );
            }
            $insert += $extra;
            array_push($insert_batch, $insert);
        }
        
        return $insert_batch;
    }
    
    // 设置每一列的宽度
    public function set_col_width($obj, $data)
    {
        foreach ($data as $k => $v) {
            $obj->getActiveSheet()
                ->getColumnDimension($v['row'])
                ->setWidth($v['width']);
        }
    }

    /**
     * 合并单元格
     * 
     * @param object $obj            
     * @param array $merge
     *            pRow是行数(1开始)
     *            
     */
    public function export_mergeCells($obj, $merge)
    {
        foreach ($merge as $key => $value) {
            foreach ($value as $k => $v) {
                $obj->getActiveSheet()->mergeCellsByColumnAndRow($v['pColumn1'], $v['pRow1'], $v['pColumn2'], $v['pRow2']);
            }
        }
    }

    public function export_excel_data($fields, $data, $title = "", $description = "导出列表", $param = "", $merge = "")
    {
        // p($data);die;
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()
            ->setTitle($title)
            ->setDescription($description);
        
        // 设置列宽度
        if ($param !== "") {
            $this->set_col_width($objPHPExcel, $param);
        }
        
        foreach ($fields as $key => $value) {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value);
        }
        
        $row = 2;
        foreach ($data as $key => $value) {
            foreach ($value as $k => $v) {
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, $row, $v);
            }
            $row ++;
        }
        if ($merge != '') {
            $this->export_mergeCells($objPHPExcel, $merge);
        }
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="export.xls"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }
}
?>